mysql join sum时数据重复问题及解决方案 | 您所在的位置:网站首页 › sql两次inner join › mysql join sum时数据重复问题及解决方案 |
当我们使用mysql的join功能从多张表中取出数据并使用sum分别对取出的数据求和时 会发现sum出来的值是不对的,往往是正确值的整数倍 为什么会出现这样的情况呢 复现 假设有两张表:user_buy 和user_sell,分别记录了用户在某天的购买和出售金额, 结构如下: CREATE TABLE `user_buy` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `uid` int(10) unsigned NOT NULL COMMENT '用户id', `amount` int(10) unsigned default '0' COMMENT '数量', `init_time` date not null comment '日期', PRIMARY KEY (`id`), KEY `uid` (`uid`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户购买统计';
CREATE TABLE `user_sell` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `uid` int(10) unsigned NOT NULL COMMENT '用户id', `amount` int(10) unsigned default '0' COMMENT '数量', `init_time` date not null comment '日期', PRIMARY KEY (`id`), KEY `uid` (`uid`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户出售统计';
数据如下:
现在我想求用户在一段时间里面的购买总金额 - 出售总金额,并根据差值排序,取前30个用户 一番思考后,我写下了如下sql select ub.uid, sum(ub.amount) - sum(us.amount) total from user_buy ubleft join user_sell us on ub.uid = us.uidgroup by ub.uidorder by total limit 30
得到如下结果:
很明显,这个结果是不对的 用户11的正确值应该是(100+200) - (10 + 20) = 270 用户22的正确值应该是(300+400) -(30 + 40) = 630 sql得出的结果是正确值的2倍! 猜想 由于按照uid字段进行聚合,且uid也是两个表关联的联结字段,因此会出现以下情况: 1.user_buy中的某个uid在user_sell中存在,且在user_sell中有n条记录时,会使得sum(ub.amount)的值变为正常值的n倍 2.user_sell中的某个uid在user_buy中存在,且在user_buy中有m条记录时,会使得sum(us.amount)的值变为正常值的m倍 验证 修改数据,在user_sell中增加一条uid = 11的数据
继续用上面的sql查询:
用户11的正确值应该是(100+200) - (10 + 20+30) = 240 780 怎么来的? (100+200)* 3 - (10 + 20 +30)* 2 = 780 1260同理,猜想正确 解决方案 为了避免联表字段同时满足多条记录的情况 先用子查询在各自表中完成数据的聚合,将数据存放在临时表中,再联合临时表 此时两个临时表中的数据对聚合字段uid来说都是唯一的 sql如下: select ub.uid, sum(ub.amount) - sum(us.amount) total from (select uid, sum(amount) as amount from user_buy group by uid) as ubleft join (select uid, sum(amount) as amount from user_sell group by uid) as us on ub.uid = us.uidgroup by ub.uidorder by total limit 30
结果:
|
CopyRight 2018-2019 实验室设备网 版权所有 |